import datetime

from flask import Flask, render_template, request, session
import pymysql
from flask_session import Session

# 数据库配置
conn = pymysql.connect(host='127.0.0.1', port=3306, user="root",
                       password="123456", charset="utf8", db='room')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

app = Flask(__name__, template_folder="templates")
app.config['SESSION_TYPE'] = 'filesystem'
app.secret_key = 'saognakpoxmkcvmp-dfnano23nf/*-dandsfnxclmos'
Session(app)


# 登录路由
@app.route('/', methods=['GET', 'POST'])
def login():
    if request.method == 'GET':
        session['username'] = ''
        return render_template('login.html')
    username = request.form.get('username')
    password = request.form.get('password')
    sql = 'select * from login where username=%s and password=%s'
    cursor.execute(sql, (username, password))
    data = cursor.fetchall()
    if data != ():
        role = data[0]['role']
        if role == 'A':
            session['username'] = username
            sql = 'select * from room_info'
            cursor.execute(sql)
            data = cursor.fetchall()
            dit = {
                'data': data,
                'username': session['username']
            }
            return render_template('admin.html', data=dit)
        session['username'] = username
        sql = 'select * from room_info'
        cursor.execute(sql)
        data = cursor.fetchall()
        dit = {
            'data': data,
            'username': session['username']
        }
        return render_template('user_room.html', data=dit)
    return render_template('login.html')


# 管理员
@app.route('/admin/', methods=['GET', 'POST'])
def admin():
    if request.method == 'GET':
        sql = 'select * from room_info'
        cursor.execute(sql)
        data = cursor.fetchall()
        dit = {
            'data': data,
            'username': session['username']
        }
        return render_template('admin.html', data=dit)
    room_number = request.form.get('room_number')
    room_name = request.form.get('room_name')
    room_info = request.form.get('room_info')
    sql = 'insert into room_info values (%s, %s, %s, %s)'
    cursor.execute(sql, (room_number, room_name, room_info,1))
    conn.commit()
    sql = 'select * from room_info'
    cursor.execute(sql)
    data = cursor.fetchall()
    dit = {
        'data': data,
        'username': session['username']
    }
    return render_template('admin.html', data=dit)

# 教室信息查询
@app.route('/room/info', methods=['GET', 'POST'])
def room_info():
    if request.method == 'GET':
        sql = 'select * from room_info'
        cursor.execute(sql)
        data = cursor.fetchall()
        dit = {
            'data': data,
            'username': session['username']
        }
        return render_template('user_room.html', data=dit)
    getRoomNumber = request.form.get('getRoomNumber')
    sql = 'update room_info set room_flag=%s where room_number=%s'
    cursor.execute(sql, (0, getRoomNumber))
    conn.commit()
    sql = 'select * from room_info where room_number=%s'
    cursor.execute(sql, (getRoomNumber,))
    data = cursor.fetchall()
    room_number = data[0]['room_number']
    room_name = data[0]['room_name']
    room_info = data[0]['room_info']
    username = session['username']
    date = datetime.datetime.now()
    sql = 'insert into res values (%s,%s,%s,%s,%s)'
    cursor.execute(sql, (room_number, room_name, room_info, date, username))
    conn.commit()
    sql = 'select * from room_info'
    cursor.execute(sql)
    data = cursor.fetchall()
    dit = {
        'data': data,
        'username': session['username']
    }
    return render_template('user_room.html', data=dit)


# 预约记录
@app.route('/room/res', methods=['GET', 'POST'])
def room_res():
    if request.method == 'GET':
        username = session['username']
        sql = 'select * from res where res_username=%s'
        cursor.execute(sql, (username,))
        data = cursor.fetchall()
        dit = {
            'data': data,
            'username': session['username']
        }
        return render_template('user_res.html', data=dit)
    username = session['username']
    getRoomNumber = request.form.get('getRoomNumber')
    sql = 'delete from res where room_number=%s and res_username=%s'
    cursor.execute(sql, (getRoomNumber, username))
    conn.commit()
    # 释放该教室
    sql = 'update room_info set room_flag=%s where room_number=%s'
    cursor.execute(sql, (1, getRoomNumber))
    conn.commit()
    sql = 'select * from res where res_username=%s'
    cursor.execute(sql, (username,))
    data = cursor.fetchall()
    dit = {
        'data': data,
        'username': session['username']
    }
    return render_template('user_res.html', data=dit)


# 个人信息管理
@app.route('/user/info', methods=['POST', 'GET'])
def user_info():
    if request.method == 'GET':
        username = session['username']
        sql = 'select * from login where username=%s'
        cursor.execute(sql, (username,))
        data = cursor.fetchall()
        dit = {
            'data': data,
            'username': session['username']
        }
        return render_template('user_info.html', data=dit)
    newPassword = request.form.get('newPassword')
    username = session['username']
    sql = 'update login set password=%s where username=%s'
    cursor.execute(sql, (newPassword, username))
    conn.commit()
    sql = 'select * from login where username=%s'
    cursor.execute(sql, (username,))
    data = cursor.fetchall()
    dit = {
        'data': data,
        'username': session['username']
    }
    return render_template('user_info.html', data=dit)


if __name__ == '__main__':
    app.run()
